# db/mysql/dao/customers.py
import random
from datetime import datetime
from typing import List, Optional

from sqlalchemy import and_, func
from sqlalchemy.orm import Session
from sqlalchemy.exc import SQLAlchemyError

from db.mysql.customer_platform import CustomerPlatform, PlatformType
from db.mysql.customers import Customers, CustomerSourceEnum
from core.database import SessionLocal
import logging

logger = logging.getLogger(f"app.{__name__}")


class CustomersDAO:
    def __init__(self, db: Session = None):
        self.db = db or SessionLocal()

    def create_customer(self, customer_data: dict) -> Optional[Customers]:
        """
        创建新客户

        Args:
            customer_data: 客户数据字典

        Returns:
            Customers: 创建的客户对象或 None
        """
        try:
            db_customer = Customers(**customer_data)
            self.db.add(db_customer)
            self.db.commit()
            self.db.refresh(db_customer)
            logger.info(f"客户创建成功: {db_customer.customer_name}")
            return db_customer
        except SQLAlchemyError as e:
            self.db.rollback()
            logger.error(f"创建客户失败: {str(e)}")
            return None

    def get_customer_by_id(self, customer_id: int) -> Optional[Customers]:
        """
        根据ID获取客户

        Args:
            customer_id: 客户ID

        Returns:
            Customers: 客户对象或 None
        """
        try:
            return self.db.query(Customers).filter(Customers.id == customer_id).first()
        except SQLAlchemyError as e:
            logger.error(f"查询客户失败 (ID: {customer_id}): {str(e)}")
            return None

    def get_customers_by_id(self, customer_ids: List[int], page: int = 1, page_size: int = 10):
        """
        根据客户ID列表分页获取客户信息

        Args:
            customer_ids: 客户ID列表
            page: 页码，默认为1
            page_size: 每页大小，默认为10

        Returns:
            tuple: (客户列表, 总记录数)
        """
        skip = (page - 1) * page_size
        query = self.db.query(Customers).filter(Customers.id.in_(customer_ids))
        # 获取总记录数
        total_count = query.count()
        # 分页查询
        customers = query.offset(skip).limit(page_size).all()
        return customers, total_count

    def get_customer_by_customer_id(self, customer_id: str) -> Optional[Customers]:
        """
        根据customer_id获取客户

        Args:
            customer_id: 客户唯一标识符

        Returns:
            Customers: 客户对象或 None
        """
        try:
            return self.db.query(Customers).filter(Customers.customer_id == customer_id).first()
        except SQLAlchemyError as e:
            logger.error(f"查询客户失败 (customer_id: {customer_id}): {str(e)}")
            return None

    def get_customer_by_name(self, customer_name: str) -> Optional[Customers]:
        """
        根据客户名称获取客户

        Args:
            customer_name: 客户名称

        Returns:
            Customers: 客户对象或 None
        """
        try:
            return self.db.query(Customers).filter(Customers.customer_name == customer_name).first()
        except SQLAlchemyError as e:
            logger.error(f"查询客户失败 (客户名称: {customer_name}): {str(e)}")
            return None

    def get_customers(self, skip: int = 0, limit: int = 100,
                      customer_name: str = None, phone: str = None, city: str = None,
                      source: str = None, status: int = None, in_call_task: int = None,
                      user_id: str = None):
        """
        获取客户列表，支持多种过滤条件

        Args:
            skip: 跳过的记录数
            limit: 每页记录数
            customer_name: 客户姓名搜索
            phone: 手机号搜索
            city: 城市搜索
            source: 来源搜索
            status: 状态过滤
            in_call_task: 是否在轮呼任务中
            user_id: 用户ID过滤

        Returns:
            tuple: (客户列表, 总记录数)
        """
        query = self.db.query(Customers)

        # 添加搜索条件
        if customer_name:
            query = query.filter(Customers.customer_name.like(f"%{customer_name}%"))
        if phone:
            query = query.filter(Customers.phone.like(f"%{phone}%"))
        if city:
            query = query.filter(Customers.city.like(f"%{city}%"))
        if source:
            query = query.filter(Customers.source == source)
        if in_call_task is not None:
            query = query.filter(Customers.in_call_task == in_call_task)
        if status is not None:
            query = query.filter(Customers.status == status)
        # 添加用户ID过滤条件
        if user_id is not None:
            query = query.filter(Customers.user_id == user_id)

        # 按创建时间倒序排列
        query = query.order_by(Customers.created_at.desc())
        # 获取总记录数
        total_count = query.count()

        # 分页查询
        customers = query.offset(skip).limit(limit).all()

        return customers, total_count

    def get_random_customers(self, user_id: str, count: int = 20, called_count_order: str = "asc", in_call_task: int = 0):
        """
        获取指定用户的随机客户（使用数据库随机函数）

        Args:
            user_id: 用户ID
            count: 随机选择的客户数量，默认20

        Returns:
            list: 随机客户对象列表
        """
        try:
            # 构建查询条件
            query = self.db.query(Customers).filter(
                Customers.user_id == user_id,
                Customers.status == 1,
                Customers.in_call_task == in_call_task
            )
            # 根据排序参数添加排序
            if called_count_order.lower() == "asc":
                query = query.order_by(Customers.called_count.asc())
            elif called_count_order.lower() == "desc":
                query = query.order_by(Customers.called_count.desc())
            # 添加随机排序并限制数量
            customers = query.order_by(func.rand()).limit(count).all()
            return customers
        except SQLAlchemyError as e:
            logger.error(f"获取随机客户失败 (user_id: {user_id}): {str(e)}")
            raise

    def update_customer(self, customer_id: str, customer_data: dict, auto_commit=True) -> Optional[Customers]:
        """
        根据customer_id更新客户信息

        Args:
            customer_id: 客户唯一标识符（字符串）
            customer_data: 要更新的客户数据
            auto_commit: 是否自动提交事务

        Returns:
            Customers: 更新后的客户对象或 None
        """
        try:
            # 根据customer_id（字符串唯一ID）查找客户
            db_customer = self.get_customer_by_customer_id(customer_id)
            if db_customer:
                # 过滤掉空值（None, '', [], {} 等）
                filtered_data = {
                    key: value for key, value in customer_data.items()
                    if value is not None and value != '' and not (
                                isinstance(value, (list, dict)) and len(value) == 0)
                }
                for key, value in filtered_data.items():
                    setattr(db_customer, key, value)
                if auto_commit:
                    self.db.commit()
                    self.db.refresh(db_customer)
                else:
                    self.db.flush()
                logger.info(f"客户更新成功: {db_customer.customer_name}")
                return db_customer
            return None
        except SQLAlchemyError as e:
            logger.error(f"更新客户失败 (customer_id: {customer_id}): {str(e)}")
            if auto_commit:
                self.db.rollback()
            else:
                raise  # 重新抛出异常让外层处理

    def update_customer_by_customer_id(self, customer_id: str, customer_data: dict, auto_commit=True) -> Optional[
        Customers]:
        """
        """
        try:
            db_customer = self.get_customer_by_customer_id(customer_id)
            if db_customer:
                # 过滤掉空值（None, '', [], {} 等）
                filtered_data = {
                    key: value for key, value in customer_data.items()
                    if value is not None and value != '' and not (
                                isinstance(value, (list, dict)) and len(value) == 0)
                }
                for key, value in filtered_data.items():
                    setattr(db_customer, key, value)
                if auto_commit:
                    self.db.commit()
                    self.db.refresh(db_customer)
                else:
                    self.db.flush()
                logger.info(f"客户更新成功: {db_customer.customer_name}")
                return db_customer
            return None
        except SQLAlchemyError as e:
            logger.error(f"更新客户失败 (ID: {customer_id}): {str(e)}")
            if auto_commit:
                self.db.rollback()
            else:
                raise  # 重新抛出异常让外层处理

    def delete_customer(self, customer_id: int) -> bool:
        """
        删除客户

        Args:
            customer_id: 客户ID

        Returns:
            bool: 删除是否成功
        """
        try:
            db_customer = self.get_customer_by_id(customer_id)
            if db_customer:
                self.db.delete(db_customer)
                self.db.commit()
                logger.info(f"客户删除成功: ID {customer_id}")
                return True
            return False
        except SQLAlchemyError as e:
            self.db.rollback()
            logger.error(f"删除客户失败 (ID: {customer_id}): {str(e)}")
            return False

    def bulk_create_customers(self, customers_data: List[dict]) -> tuple:
        """
        批量创建客户

        Args:
            customers_data: 客户数据列表

        Returns:
            tuple: (成功数量, 失败数量, 错误信息列表)
        """
        success_count = 0
        fail_count = 0
        errors = []
        # 收集所有手机号以便一次性检查
        phones_in_data = [str(customer_data.get('phone')) for customer_data in customers_data
                          if customer_data.get('phone')]
        try:
            # 先检查数据库中已存在的手机号
            existing_customers = self.db.query(Customers.phone).filter(
                Customers.phone.in_(phones_in_data)
            ).all()
            existing_phones = {str(customer.phone) for customer in existing_customers}
            for i, customer_data in enumerate(customers_data):
                try:
                    # 检查手机号是否已存在
                    phone = customer_data.get('phone')
                    if not phone:
                        errors.append(f"行 {i + 1}: 手机号不能为空")
                        fail_count += 1
                        continue

                    # existing_customer = self.get_customer_by_phone(phone)
                    if str(phone) in existing_phones:
                        errors.append(f"行 {i + 1}: 手机号 {phone} 已存在")
                        fail_count += 1
                        continue

                    # 创建新客户
                    db_customer = Customers(**customer_data)
                    self.db.add(db_customer)
                    success_count += 1
                except Exception as e:
                    errors.append(f"行 {i + 1}: {str(e)}")
                    fail_count += 1
                    logger.error(f"行 {i + 1}: 创建客户失败: {str(e)}")

            self.db.commit()
            logger.info(f"批量导入客户完成: 成功 {success_count}, 失败 {fail_count}")
            return success_count, fail_count, errors
        except SQLAlchemyError as e:
            self.db.rollback()
            logger.error(f"批量导入客户失败: {str(e)}")
            return 0, len(customers_data), [f"批量导入失败: {str(e)}"]

    def batch_create_customers(self, customers_data: List[dict]) -> tuple:
        """
        批量创建客户（改进版，处理重复数据并返回创建的客户列表）

        Args:
            customers_data: 客户数据列表

        Returns:
            tuple: (创建成功的客户列表, 失败数量, 错误信息列表)
        """
        success_customers = []
        fail_count = 0
        errors = []

        # 收集所有手机号以便一次性检查
        phones_in_data = [str(customer_data.get('phone')) for customer_data in customers_data
                          if customer_data.get('phone')]

        try:
            # 先检查数据库中已存在的手机号
            existing_customers = self.db.query(Customers.phone,Customers.customer_id).filter(
                Customers.phone.in_(phones_in_data)
            ).all()
            existing_phones = {str(customer.phone) for customer in existing_customers}

            for i, customer_data in enumerate(customers_data):
                try:
                    # 检查手机号是否已存在
                    phone = customer_data.get('phone')
                    if not phone:
                        errors.append(f"行 {i + 1}: 手机号不能为空")
                        fail_count += 1
                        continue

                    if str(phone) in existing_phones:
                        errors.append(f"行 {i + 1}: 手机号 {phone} 已存在")
                        fail_count += 1
                        continue

                    # 创建新客户
                    db_customer = Customers(**customer_data)
                    self.db.add(db_customer)
                    self.db.flush()  # 获取ID但不提交事务
                    success_customers.append(db_customer)
                except Exception as e:
                    errors.append(f"行 {i + 1}: {str(e)}")
                    fail_count += 1
                    logger.error(f"行 {i + 1}: 创建客户失败: {str(e)}")

            self.db.commit()
            logger.info(f"批量创建客户完成: 成功 {len(success_customers)}, 失败 {fail_count}")
            return success_customers, existing_customers, fail_count, errors
        except SQLAlchemyError as e:
            self.db.rollback()
            logger.error(f"批量创建客户失败: {str(e)}")
            return [], len(customers_data), [f"批量创建失败: {str(e)}"]

    def get_customer_by_phone(self, phone: str) -> Optional[Customers]:
        """
        根据手机号获取客户

        Args:
            phone: 手机号

        Returns:
            Customers: 客户对象或 None
        """
        try:
            return self.db.query(Customers).filter(Customers.phone == phone).first()
        except SQLAlchemyError as e:
            logger.error(f"查询客户失败 (手机号: {phone}): {str(e)}")
            return None

    def close(self):
        """
        关闭数据库会话
        """
        self.db.close()

    def create_customer_platform(self, customer_id: str, platform: str, status: str):
        try:
            customer_platform = CustomerPlatform(
                customer_id=customer_id,
                platform=platform,
                status=status,
                request_time=datetime.now()  # 添加请求时间
            )
            self.db.add(customer_platform)
            self.db.commit()
            self.db.refresh(customer_platform)
            logger.info(f"客户平台记录创建成功: customer_id={customer_id}, platform={platform}")
            return customer_platform
        except SQLAlchemyError as e:
            self.db.rollback()
            logger.error(f"创建客户平台记录失败: {str(e)}")
            raise

    def update_customer_platform(self, customer_id: str, platform: str, status: str = None,
                                 request_time: datetime = None, added_time: datetime = None):

        try:
            # 构建更新字段字典
            update_fields = {}

            # 只有当参数不为None时才添加到更新字段中
            if status is not None:
                update_fields[CustomerPlatform.status] = status

            if request_time is not None:
                update_fields[CustomerPlatform.request_time] = request_time

            if added_time is not None:
                update_fields[CustomerPlatform.added_time] = added_time
            # 如果没有任何字段需要更新，则直接返回
            if not update_fields:
                logger.info(f"没有需要更新的字段: customer_id={customer_id}, platform={platform}")
                return True

            # 执行更新操作
            result = self.db.query(CustomerPlatform).filter(
                and_(
                    CustomerPlatform.customer_id == customer_id,
                    CustomerPlatform.platform == platform
                )
            ).update(update_fields)

            self.db.commit()
            # result返回受影响的行数
            if result > 0:
                logger.info(f"客户平台记录更新成功: customer_id={customer_id}, platform={platform}")
                return True
            else:
                logger.info(f"未找到匹配的客户平台记录: customer_id={customer_id}, platform={platform}")
                return False

        except SQLAlchemyError as e:
            self.db.rollback()
            logger.error(f"更新客户平台记录失败: {str(e)}")
            raise

    def get_customer_platform(self, customer_id: str, platform: str) -> Optional[CustomerPlatform]:
        try:
            return self.db.query(CustomerPlatform).filter(
                and_(
                    CustomerPlatform.customer_id == customer_id,
                    CustomerPlatform.platform == platform
                )
            ).first()
        except SQLAlchemyError as e:
            logger.error(f"查询客户平台记录失败 (customer_id: {customer_id}, platform: {platform}): {str(e)}")
            return None

    def get_customers_without_platform(self, platform: str = None, skip: int = 0, limit: int = 100) -> tuple:
        """
        获取在客户表中有数据但在客户平台表中没有记录的客户

        Args:
            platform: 平台名称，如果指定则只查找在该平台没有记录的客户
            skip: 跳过的记录数
            limit: 每页记录数

        Returns:
            tuple: (客户列表, 总记录数)
        """
        try:
            # 创建子查询，查找在CustomerPlatform表中存在的客户ID
            subquery = self.db.query(CustomerPlatform.customer_id)

            # 如果指定了平台，则只查找在该平台没有记录的客户
            if platform:
                subquery = subquery.filter(CustomerPlatform.platform == platform)

            # 获取子查询结果
            subquery = subquery.subquery()

            # 主查询：查找在Customers表中存在但在子查询结果中不存在的客户
            query = self.db.query(Customers).filter(
                ~Customers.customer_id.in_(subquery)
            )

            # 获取总记录数
            total_count = query.count()

            # 分页查询
            customers = query.offset(skip).limit(limit).all()

            logger.info(f"查询到 {total_count} 个在客户平台表中没有记录的客户")
            return customers, total_count

        except SQLAlchemyError as e:
            logger.error(f"查询无平台记录的客户失败: {str(e)}")
            return [], 0

    def get_customers_without_platform_orm(self, platform: str = PlatformType.WECHAT, skip: int = 0,
                                           limit: int = 100) -> tuple:
        try:
            # 查找在指定平台没有记录的客户
            query = self.db.query(Customers).outerjoin(
                CustomerPlatform,

                and_(
                    Customers.customer_id == CustomerPlatform.customer_id,
                    CustomerPlatform.platform == platform
                )
            ).filter(CustomerPlatform.customer_id.is_(None))

            # 获取总记录数
            total_count = query.count()

            # 分页查询
            customers = query.offset(skip).limit(limit).all()

            logger.info(f"LEFT JOIN方式查询到 {total_count} 个在 {platform} 平台中没有记录的客户")
            return customers, total_count

        except SQLAlchemyError as e:
            logger.error(f"LEFT JOIN方式查询无 {platform} 平台记录的客户失败: {str(e)}")
            return [], 0

    def get_customer_by_external_id(self, external_id: str) -> Optional[Customers]:
        try:
            return self.db.query(Customers).filter(Customers.external_id == external_id).first()
        except SQLAlchemyError as e:
            logger.error(f"查询客户失败 (外部ID: {external_id}): {str(e)}")
            return None

    def get_following_customers(self, skip: int = 0, limit: int = 100,
                                customer_name: str = None, phone: str = None, city: str = None,
                                source: str = None):
        """
        获取跟进客户列表
        """
        from db.mysql.customers import ListTypeEnum

        query = self.db.query(Customers).filter(Customers.list_type == ListTypeEnum.FOLLOWING)

        # 添加搜索条件
        if customer_name:
            query = query.filter(Customers.customer_name.like(f"%{customer_name}%"))
        if phone:
            query = query.filter(Customers.phone.like(f"%{phone}%"))
        if city:
            query = query.filter(Customers.city.like(f"%{city}%"))
        if source:
            query = query.filter(Customers.source == source)

        # 获取总记录数
        total_count = query.count()

        # 分页查询
        customers = query.offset(skip).limit(limit).all()

        return customers, total_count

    def batch_update_following_customers(self, customer_ids: List[str]) -> int:
        """
        批量更新客户状态为"我跟进的客户"

        Args:
            customer_ids: 客户ID列表

        Returns:
            int: 成功更新的客户数量
        """
        try:
            from db.mysql.customers import ListTypeEnum

            # 更新指定ID的客户的状态为"following"(我跟进的客户)
            result = self.db.query(Customers).filter(
                Customers.customer_id.in_(customer_ids)
            ).update(
                {Customers.list_type: ListTypeEnum.FOLLOWING},
                synchronize_session=False
            )

            self.db.commit()
            logger.info(f"批量更新客户跟进状态成功: {result} 位客户")
            return result

        except SQLAlchemyError as e:
            self.db.rollback()
            logger.error(f"批量更新客户跟进状态失败: {str(e)}")
            return 0

    def batch_update_call_task_customers(self, customer_ids: List[str], auto_commit: bool = True):
        """
        根据主键id进行查找
                批量更新客户是否在轮呼状态列表中的状态 以及当前任务状态

                Args:
                    customer_ids: 客户ID列表

                Returns:
                    int: 成功更新的客户数量
                """
        try:
            result = self.db.query(Customers).filter(Customers.customer_id.in_(customer_ids)).update(
                {"in_call_task": 0, "current_task_id": None, "called_count": Customers.called_count + 1}
            )
            if auto_commit:
                self.db.commit()
                logger.info(f"批量更新客户跟进状态成功: {result} 位客户")
            else:
                self.db.flush()
            return result
        except Exception as e:
            logger.error(f"批量更新客户状态失败: {e}")
            if auto_commit:
                self.db.rollback()
            else:
                raise